#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/

$HIVE_CMD -S -f "/root/Liam/online_edu/交付文件/create_test.sql"


${HIVE_HOME} -S -e "
-- todo dws层建表 使用hive
create database if not exists edu_dws;
use edu_dws;

drop table if exists edu_dws.customer_relationship_daycount;
create table if not exists edu_dws.customer_relationship_daycount(
    -- todo 维度
    -- 日期（天）
    date_day        string comment '日期（天）',
    -- 线上线下
    origin_type     string comment '线上线下',
    -- 新老学员
    customer_state  string comment '新老学员',
    -- 地区
    area            string comment '地区',
    -- 学科
    subject         string comment '学科',
    -- 校区
    school          string comment '校区',
    -- 来源渠道
    origin_channel  string comment '来源渠道',
    -- 咨询中心
    department_name string comment '咨询中心',

    -- todo 指标
    -- 意向用户个数
    customer_relationship_amt bigint comment '意向用户个数',

    -- 维度分组标记
    group_type      string comment '维度分组标记,分组类型：1-日期，2-地区，3-学科，4-校区，5-来源渠道，6-咨询中心'
)comment '意向用户数量日统计'
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists edu_dws.customer_clue_count;
create table if not exists edu_dws.customer_clue_count(
    -- todo 维度
    -- 日期
    dim_date        string comment '日期维度',
    -- 时间
    dim_time        string comment '时间维度',
    -- 线上线下
    origin_type     string comment '线上线下',
    -- 新老学员
    customer_state  string comment '新老学员',
    -- todo 指标
    -- 有效线索个数
    clue_count      bigint comment '有效线索个数',
    all_count       bigint comment '所有线索个数',

    -- 维度分组标记
    group_type      string comment '维度分组标记,分组类型：1-日期，2-时间，3-线上线下，-4-新老学员'
)comment '有效线索数统计'
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');

"
${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "
-- todo dws层数据导入 使用presto
insert into hive.edu_dws.customer_relationship_daycount
with temp as (
    select
           -- 指标
           id,
           -- 维度
           create_date_time,
           origin_type,
           customer_state,
           area,
           subject_name,
           school_name,
           origin_channel,
           department_name,
           -- 分组去重
           row_number() over (partition by id, create_date_time, origin_type, customer_state) as date_rn,
           row_number() over (partition by id, create_date_time, origin_type, customer_state, area) as area_rn,
           row_number() over (partition by id, create_date_time, origin_type, customer_state, subject_name) as subject_rn,
           row_number() over (partition by id, create_date_time, origin_type, customer_state, school_name) as school_rn,
           row_number() over (partition by id, create_date_time, origin_type, customer_state, origin_channel) as origine_rn,
           row_number() over (partition by id, create_date_time, origin_type, customer_state, department_name) as department_rn
    from edu_dwb.customer_relationship_detail
)
, t2 as (
    select
        -- 维度
        create_date_time as date_day,
        origin_type,
        customer_state,
        area,
        subject_name as subject,
        school_name as school,
        origin_channel,
        department_name,
        -- 指标
--         count(*) as customer_relationship_amt,
        case
            when grouping(create_date_time, origin_type, customer_state, area) = 0
                then count(area)
            when grouping(create_date_time, origin_type, customer_state, subject_name) = 0
                then count(subject_name)
            when grouping(create_date_time, origin_type, customer_state, school_name) = 0
                then count(school_name)
            when grouping(create_date_time, origin_type, customer_state, origin_channel) = 0
                then count(origin_channel)
            when grouping(create_date_time, origin_type, customer_state, department_name) = 0
                then count(department_name)
            when grouping(create_date_time, origin_type, customer_state) = 0
                then count(customer_state)
            else 0
            end as customer_relationship_amt,
        -- 分组标记
        case
            when grouping(create_date_time, origin_type, customer_state, area) = 0
                then '地区'
            when grouping(create_date_time, origin_type, customer_state, subject_name) = 0
                then '学科'
            when grouping(create_date_time, origin_type, customer_state, school_name) = 0
                then '校区'
            when grouping(create_date_time, origin_type, customer_state, origin_channel) = 0
                then '渠道'
            when grouping(create_date_time, origin_type, customer_state, department_name) = 0
                then '部门'
            when grouping(create_date_time, origin_type, customer_state) = 0
                then '时间'
            else '其他'
            end as group_type
    from temp
    where date_rn = 1
      and area_rn = 1
      and subject_rn = 1
      and school_rn = 1
      and origine_rn = 1
      and department_rn = 1
    group by
        grouping sets
        ( (create_date_time, origin_type, customer_state),
          (create_date_time, origin_type, customer_state, area),
          (create_date_time, origin_type, customer_state, subject_name),
          (create_date_time, origin_type, customer_state, school_name),
          (create_date_time, origin_type, customer_state, origin_channel),
          (create_date_time, origin_type, customer_state, department_name)
        )
)
select * from t2
-- where group_type = '地区' and area is not null
;


insert into hive.edu_dws.customer_clue_count
select
        dim_date,
        null as dim_hour,
        origin_type,
        customer_state,
        count(appeal_status = '有效') as clue_count,
        count(id) as all_count,
        'day' as group_type
from hive.edu_dws.temp
-- where appeal_status = '有效'
group by dim_date, origin_type, customer_state
union all
select
        dim_date,
        dim_hour,
        origin_type,
        customer_state,
        count(appeal_status = '有效') as clue_count,
        count(id) as all_count,
        'hour' as group_type
from hive.edu_dws.temp
group by dim_date, dim_hour, origin_type, customer_state
-- having count(appeal_status = '有效') > 0
;


-- todo dm层建表
drop database if exists edu_dm;
create database if not exists edu_dm;

drop table if exists edu_dm.customer_relationship_count;
create table if not exists edu_dm.customer_relationship_count(
    -- 时间粒度标记
    time_type                 string comment '时间粒度标记,时间粒度：1-年，2-月，3-日',
    -- 时间粒度字段
    year_code                 string comment '年维度',
    month_code                string comment '月维度',
    day_code                  string comment '日维度',
    year_month                string comment '年月',
    -- 分组标记
    group_type                string comment '维度分组标记,分组类型：1-日期（没有日期、日期转移到了time_type），2-地区，3-学科，4-校区，5-来源渠道，6-咨询中心',
    -- 分组维度
    dim_date                  string comment '日期维度',
    origin_type               string comment '线上线下',
    customer_state            string comment '新老学员',
    area                      string comment '地区',
    subject                   string comment '学科',
    school                    string comment '校区',
    origin_channel            string comment '来源渠道',
    department_name           string comment '咨询中心',
    -- todo =======统计=======
    customer_relationship_amt          bigint comment '意向用户统计'
)comment '意向用户数量粒度分析统计'
row format delimited fields terminated by '\t'
stored as textfile
;

drop table if exists edu_dm.customer_clue_count;
create table if not exists edu_dm.customer_clue_count(
    -- 时间粒度标记
    time_type                 string comment '时间粒度标记,时间粒度：1-天，2-小时',
    -- 时间粒度字段
    dim_date                  string comment '日期维度',
    hour_code                 string comment '小时维度',
    -- 分组维度
    origin_type               string comment '线上线下',
    customer_state            string comment '新老学员',
    -- todo =======统计=======
    clue_cnt                  bigint comment '有效线索统计',
    all_cnt                   bigint comment '总线索统计'
)comment '线索维度粒度分析统计'
row format delimited fields terminated by '\t'
stored as textfile
;

-- todo dm层数据导入
insert into hive.edu_dm.customer_relationship_count
with t1 as (
    select
--            null                      as time_type,
           substring(date_day, 1, 4) as year_code,      --
           substring(date_day, 6, 2) as month_code,     --
           substring(date_day, 9, 2) as day_code,       --
           substring(date_day, 1, 7) as year_month,     --
           group_type                as old_group_type, --
           date_day                  as dim_date,       --
           origin_type,                                 --
           customer_state,                              --
           area,                                        --
           subject,                                     --
           school,                                      --
           origin_channel,                              --
           department_name,                             --
           customer_relationship_amt

    from hive.edu_dws.customer_relationship_daycount
), t2 as (
select
       -- 时间维度
        case when grouping(year_code, month_code, year_month, day_code, dim_date) = 0
            then '天'
            when grouping(year_code, month_code, year_month) = 0
            then '月'
            when grouping(year_code) = 0
            then '年'
        end as time_type,
        year_code,
        month_code,
        day_code,
        year_month,

       -- 产品维度
       case when grouping(old_group_type, origin_type, customer_state, area) = 0
            then '地区'
            when grouping(old_group_type, origin_type, customer_state, subject) = 0
            then '学科'
            when grouping(old_group_type, origin_type, customer_state, school) = 0
            then '校区'
            when grouping(old_group_type, origin_type, customer_state, origin_channel) = 0
            then '来源渠道'
            when grouping(old_group_type, origin_type, customer_state, department_name) = 0
            then '咨询中心'
            when grouping(old_group_type, origin_type, customer_state) = 0
            then '全部'
        end as group_type,
       dim_date,
       origin_type,
       customer_state,
       area,
       subject,
       school,
       origin_channel,
       department_name,
       -- 统计值
       sum(customer_relationship_amt) as customer_relationship_amt,
       old_group_type

from t1
group by
grouping sets(
-- 年
    (old_group_type, year_code, origin_type, customer_state),
    (old_group_type, year_code, origin_type, customer_state, area),
    (old_group_type, year_code, origin_type, customer_state, subject),
    (old_group_type, year_code, origin_type, customer_state, school),
    (old_group_type, year_code, origin_type, customer_state, origin_channel),
    (old_group_type, year_code, origin_type, customer_state, department_name),

-- 月
    (old_group_type, year_code, month_code, year_month, origin_type, customer_state),
    (old_group_type, year_code, month_code, year_month, origin_type, customer_state, area),
    (old_group_type, year_code, month_code, year_month, origin_type, customer_state, subject),
    (old_group_type, year_code, month_code, year_month, origin_type, customer_state, school),
    (old_group_type, year_code, month_code, year_month, origin_type, customer_state, origin_channel),
    (old_group_type, year_code, month_code, year_month, origin_type, customer_state, department_name),

-- 天
    (old_group_type, year_code, month_code, year_month, day_code, dim_date, origin_type, customer_state),
    (old_group_type, year_code, month_code, year_month, day_code, dim_date, origin_type, customer_state, area),
    (old_group_type, year_code, month_code, year_month, day_code, dim_date, origin_type, customer_state, subject),
    (old_group_type, year_code, month_code, year_month, day_code, dim_date, origin_type, customer_state, school),
    (old_group_type, year_code, month_code, year_month, day_code, dim_date, origin_type, customer_state, origin_channel),
    (old_group_type, year_code, month_code, year_month, day_code, dim_date, origin_type, customer_state, department_name)
))
select time_type,
       year_code,
       month_code,
       day_code,
       year_month,
       group_type,
       dim_date,
       origin_type,
       customer_state,
       area,
       subject,
       school,
       origin_channel,
       department_name,
       customer_relationship_amt
from t2
where old_group_type = group_type
;

insert into hive.edu_dm.customer_clue_count
select
        group_type as time_type,
        dim_date,
        dim_time as time_type,
        origin_type,
        customer_state,
        clue_count as clue_cnt,
        all_count as all_cnt
from hive.edu_dws.customer_clue_count
;
"